# simpleinsert.py

import os

from sqlalchemy import create_engine, text

import json

# ----------------------------
# 配置部分
# ----------------------------
DB_CONFIG = {
    'host': '192.168.37.1',
    'port': 9030,
    'user': 'root',
    'password': 'pass',  # 替换为实际密码
    'database': 'demo',
    'charset': 'utf8mb4'
}

INSERT_DIR = "insert"  # 存放处理后可插入的数据文件
TABLE_NAME = "production_line_equipment_usage"


# ----------------------------
# 数据库连接
# ----------------------------
def create_connection():
    try:
        connection_string = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
        engine = create_engine(connection_string, echo=False)

        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
            print(f" 数据库连接成功：{DB_CONFIG['host']}:{DB_CONFIG['port']}")

        return engine
    except Exception as e:
        print(f" 数据库连接失败: {e}")
        return None


# ----------------------------
# 读取 insert/ 下的所有 JSON 文件
# ----------------------------
def load_insert_data():
    files = [f for f in os.listdir(INSERT_DIR) if f.endswith('.json')]
    data_list = []

    for file in files:
        path = os.path.join(INSERT_DIR, file)
        with open(path, 'r', encoding='utf-8') as f:
            data = json.load(f)
            data_list.append(data)

    print(f" 已加载 {len(data_list)} 条待插入记录")
    return data_list


# ----------------------------
# 构建插入 SQL 语句
# ----------------------------
def build_insert_sql(table_name, columns):
    placeholders = ', '.join([f":{col}" for col in columns])
    column_names = ', '.join(columns)
    sql = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders});"
    return sql


# ----------------------------
# 批量插入数据
# ----------------------------
def batch_insert(engine, records):
    if not records:
        print(" 没有数据需要插入")
        return

    sample_record = records[0]
    columns = list(sample_record.keys())
    insert_sql = build_insert_sql(TABLE_NAME, columns)

    try:
        with engine.connect() as conn:
            conn.execute(text(insert_sql), records)
            conn.commit()
            print(f" 成功插入 {len(records)} 条记录")
    except Exception as e:
        print(f" 插入失败: {e}")


# ----------------------------
# 主程序入口
# ----------------------------
def main():
    print(f"[INFO] 开始将 JSON 数据插入到表 `{TABLE_NAME}`")

    engine = create_connection()
    if not engine:
        return

    records = load_insert_data()
    if not records:
        return

    batch_insert(engine, records)


if __name__ == "__main__":
    main()